/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.childcare.dao;

import com.aptech.childcare.entities.Staff;
import com.aptech.childcare.until.ConfigDatabase;
import com.aptech.childcare.until.MD5Password;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Xuan Hieu
 */
public class StaffDAO {

    private String lastError;
    private Connection con = null;
    private ConfigDatabase db = null;
    private MD5Password md5 = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    private final String SQL_CREATE = "INSERT INTO tbStaff VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
    private final String SQL_READ = "SELECT * FROM tbStaff";
    private final String SQL_UPDATE = "SELECT * FROM tbStaff WHERE StaffID = ?";
    private final String SQL_DELETE = "DELETE FROM tbStaff WHERE StaffID = ? AND UserName = ?";
    private final String SQL_READBYACC = "SELECT * FROM tbStaff WHERE UserName = ?";

    public StaffDAO() {
        db = new ConfigDatabase();
        md5 = new MD5Password();
    }

    /**
     *
     * @param sta the Staff to add
     * @return true if add successful, false if add fail
     */
    public boolean create(Staff sta) throws IOException {
        if (!check(sta)) {
            return false;
        }
        try {
            con = db.getConnection();
            pst = con.prepareStatement(SQL_CREATE);
            pst.setString(1, sta.getUserName());
            String pass = MD5Password.getEncodedPassword(sta.getPassWord());
            pst.setString(2, pass);
            pst.setString(3, sta.getPosition());
            pst.setString(4, sta.getFullName());
            pst.setString(5, sta.getSex());
            pst.setString(6, sta.getPathImage());
            pst.setDate(7, sta.getBirthday());
            pst.setString(8, sta.getAddress());
            pst.setString(9, sta.getPhone());
            pst.setString(10, sta.getEmail());
            pst.setDate(11, sta.getStartDate());
            pst.setDate(12, sta.getEndDate());
            if (pst.executeUpdate() > 0) {
                setLastError("Add successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL Fail !");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Add Fail!");
        return false;
    }

    /**
     *
     * @param sta the Staff to edit
     * @return true if edit successful, false if edit fail
     */
    public boolean update(Staff sta) throws IOException {
        con = db.getConnection();
        try {
            pst = con.prepareStatement(SQL_UPDATE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, sta.getStaffID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateString(4, sta.getPosition());
            rs.updateString(5, sta.getFullName());
            rs.updateString(6, sta.getSex());
            //rs.updateString(7, sta.getPathImage());
            rs.updateDate(8, sta.getBirthday());
            rs.updateString(9, sta.getAddress());
            rs.updateString(10, sta.getPhone());
            rs.updateString(11, sta.getEmail());
            rs.updateDate(12, sta.getStartDate());
            rs.updateDate(13, sta.getEndDate());
            rs.updateRow();
            setLastError("Update successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            db.closeConnection(db);
        }
        setLastError("Update fail");
        return false;
    }

    /**
     *
     * @param sta
     * @return
     */
    public boolean updateAccount(Staff sta)throws IOException {
        con = db.getConnection();
        try {
            pst = con.prepareStatement(SQL_READBYACC, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, sta.getUserName());
            rs = pst.executeQuery();
            rs.first();
            rs.updateString(5, sta.getFullName());
            rs.updateString(9, sta.getAddress());
            rs.updateString(10, sta.getPhone());
            rs.updateString(11, sta.getEmail());
            rs.updateRow();
            setLastError("Update successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            db.closeConnection(db);
        }
        setLastError("Update fail");
        return false;
    }

    /**
     *
     * @return list of Staff in database
     */
    public ArrayList<Staff> readByAll() throws IOException{
        ArrayList<Staff> list = new ArrayList<Staff>();
        try {
            con = db.getConnection();
            pst = con.prepareStatement(SQL_READ);
            rs = pst.executeQuery();
            while (rs.next()) {
                Staff sta = new Staff();
                sta.setStaffID(rs.getInt(1));
                sta.setUserName(rs.getString(2));
                sta.setPassWord(rs.getString(3));
                sta.setPosition(rs.getString(4));
                sta.setFullName(rs.getString(5));
                sta.setSex(rs.getString(6));
                sta.setPathImage(rs.getString(7));
                sta.setBirthday(rs.getDate(8));
                sta.setAddress(rs.getString(9));
                sta.setPhone(rs.getString(10));
                sta.setEmail(rs.getString(11));
                sta.setEndDate(rs.getDate(13));
                sta.setStartDate(rs.getDate(12));
                list.add(sta);
            }

        } catch (SQLException ex) {
            setLastError("SQL error!");

        } finally {
            db.closeConnection(db);
        }
        return list;
    }

    public Staff readByAcc(String UserName) throws IOException{
        Staff sta = new Staff();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(SQL_READBYACC);
            pst.setString(1, UserName);
            rs = pst.executeQuery();
            while (rs.next()) {
                sta.setUserName(rs.getString(2));
                sta.setFullName(rs.getString(5));
                sta.setAddress(rs.getString(9));
                sta.setPhone(rs.getString(10));
                sta.setEmail(rs.getString(11));

            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return sta;
    }

    /**
     *
     * @param sta
     * @param oldPass
     * @return
     */
    public boolean changePass(Staff sta, String oldPass) throws IOException{
        con = db.getConnection();
        String sql = "SELECT UserName, Password FROM tbStaff WHERE UserName = ?";
        String pass = MD5Password.getEncodedPassword(sta.getPassWord());
        String old = MD5Password.getEncodedPassword(oldPass);
        try {
            pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, sta.getUserName());
            rs = pst.executeQuery();
            rs.first();
            if (rs.getString(2).equals(old)) {
                rs.updateString(2, pass);
                rs.updateRow();
                setLastError("Change password successfully!");
                return true;
            }

        } catch (SQLException ex) {
            setLastError("SQL Error!");
            return false;
        } finally {
            db.closeConnection(db);
        }
        setLastError("Please enter your old pass again!");
        return false;
    }

    /**
     *
     * @param check account logn
     * @return
     */
    public boolean checkAcc(Staff sta) throws IOException{
        con = db.getConnection();
        String sql = "SELECT * FROM tbStaff WHERE UserName = ? AND Password = ? AND Position = ?";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, sta.getUserName());
            pst.setString(2, md5.getEncodedPassword(sta.getPassWord()));
            pst.setString(3, sta.getPosition());
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                setLastError("Login successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL error!");
            db.closeConnection(db);
            return false;
        }
        setLastError("Please check username and password again!");
        return false;
    }

    /**
     *
     * @param staffID the Staff to delete
     * @return true if delete successful, false if delete fail.
     */
    public boolean delete(int staffID, String UserName) throws IOException{
        try {
            con = db.getConnection();
            pst = con.prepareStatement(SQL_DELETE);
            pst.setInt(1, staffID);
            pst.setString(2, UserName);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete successful!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL Fail!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Delete fail!");
        return false;
    }

    /**
     *
     * @param staName the Staff needed search
     * @return list of Staff
     */
    public ArrayList<Staff> readByName(String Name) throws IOException{
        ArrayList<Staff> list = readByAll();
        ArrayList<Staff> staList = new ArrayList<Staff>();
        for (Staff sta : list) {
            if (sta.getFullName().toLowerCase().matches(".*" + Name.trim().toLowerCase() + ".*")) {
                staList.add(sta);
            }
        }
        return staList;
    }

    public boolean check(Staff sta) throws IOException{
        ArrayList<Staff> list = readByAll();
        for (Staff st : list) {
            if (sta.getUserName().equals(st.getUserName())) {
                setLastError("Nanny existed !");
                return false;
            }
        }
        return true;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
